<?php
namespace App\Http\Controllers\Business\Admin\Account;


use App\Http\Controllers\Business\Shop\V1\Admin\Admin;
use App\Models\Order;
use App\Models\Recharge;
use App\Models\Shop;
use App\Models\User;
use App\Models\City;
use App\Models\AdminUser;
use App\Models\AgentWithdrawal;
use App\Models\RedEnvelope;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Log;
use App\Utils\AreaTool;

Class AgentAccountManagement{
    //代理商分润 页面
    public static function agentProfit($cityId) {
        if(!$cityId) {
            return ['status'=>'failed', 'message'=>'参数错误'];
        }
        $data = [];
        //获取市代理的比率
        $cityRedRatio = City::where('id',$cityId)->pluck('agentratio')->first();
        $cityRedRatio = $cityRedRatio/100;
        //获取总收入
//        //获取总服务费和总红包金额
//        $totalOrderFeeAmount = '';
//        $totalOrderRedAmount = '';
//        $totalOrderAmount = DB::select("select DATE_FORMAT(created_at,'%Y-%m-%d') months,sum(orderamount) orderamount,sum(redenvelopeamount) as redenvelopeamount,cityid from 91fbw_orders where cityid='".$cityId."' and refundstatus= 0 and created_at >= '2017-03-01 00:00:00' group by months order by months desc");
//        foreach($totalOrderAmount as $orderkey=>$ordervalue){
//            $orderFeeAmount = ($ordervalue->orderamount/2)*$cityRedRatio*ENV('RECHARGE_FEE');
//            $totalOrderFeeAmount = $totalOrderFeeAmount + sprintf("%.2f",substr($orderFeeAmount,0,(stripos($orderFeeAmount,'.')+3)));
//            $orderRedAmount = ($ordervalue->redenvelopeamount/2)*$cityRedRatio;
//            $totalOrderRedAmount = $totalOrderRedAmount + sprintf("%.2f",substr($orderRedAmount,0,(stripos($orderRedAmount,'.')+3)));
//        }
//        $totalProfitAmount = $totalOrderFeeAmount - $totalOrderRedAmount;
//        $totalProfitAmount = !empty($totalProfitAmount)?$totalProfitAmount:'0.00';
        //累计已结款
        $accountWithdrawalAmount = AgentWithdrawal::where('cityid',$cityId)->where('status',1)->sum('accountamount');
        $accountWithdrawalAmount = !empty($accountWithdrawalAmount)?$accountWithdrawalAmount:'0.00';
        //累计待结款
        $accountWaitWithdrawalAmount  = AgentWithdrawal::where('cityid',$cityId)->where('status',0)->sum('accountamount');
        $accountWaitWithdrawalAmount  = !empty($accountWaitWithdrawalAmount)?$accountWaitWithdrawalAmount :'0.00';
        //获取本月的总收入
        //获取本月的订单金额
        $thismonthStartTime = date('Y-m').'-01 00:00:00';
        $thismonthLastTime = date('Y-m-d', strtotime("$thismonthStartTime +1 month -1 day")) ." 23:59:59";
        //获取本月服务费
        $thismonthOrderTotalFeeAmount = '';
        $thismonthTotalRedAmount = '';
        $thismonthOrderAmount = DB::select("select DATE_FORMAT(created_at,'%Y-%m-%d') months,sum(orderamount) orderamount,sum(redenvelopeamount) as redenvelopeamount,cityid from 91fbw_orders where cityid='".$cityId."' and refundstatus= 0 and created_at >= '2017-03-01 00:00:00' and created_at >='".$thismonthStartTime."' and created_at <='".$thismonthLastTime."' group by months order by months desc");
        foreach($thismonthOrderAmount as $orderkey=>$ordervalue){
            $thismonthOrderFeeAmount = ($ordervalue->orderamount/2)*$cityRedRatio*ENV('RECHARGE_FEE');
            $thismonthOrderTotalFeeAmount = $thismonthOrderTotalFeeAmount + sprintf("%.2f",substr($thismonthOrderFeeAmount,0,(stripos($thismonthOrderFeeAmount,'.')+3)));
            $thismonthOrderRedAmount = ($ordervalue->redenvelopeamount/2)*$cityRedRatio;
            $thismonthTotalRedAmount = $thismonthTotalRedAmount + sprintf("%.2f",substr($thismonthOrderRedAmount,0,(stripos($thismonthOrderRedAmount,'.')+3)));
        }
        $thismonthTotalProfitAmount = $thismonthOrderTotalFeeAmount - $thismonthTotalRedAmount;
        $thismonthTotalProfitAmount = !empty($thismonthTotalProfitAmount)?$thismonthTotalProfitAmount:'0.00';
        //获取所有月份的订单结款明细
        $newLists = array();
        $totalProfit = '';
        //通过订单表查找出所有的月份
        $orderCreatedMonth = Order::where('cityid',$cityId)->where('refundstatus',0)->select(DB::raw('DATE_FORMAT(created_at,"%Y-%m") months'))->groupBy('months')->orderBy('months','desc')->get();
        foreach($orderCreatedMonth as $key=>$value){
            $dayOrderAmount = '';
            $dayFeeAmount = '';
            $redEnvelopeRefundAmount = '';
            $profitAmount = '';
            $newLists[$key]['accounttime'] = $value['months'];
            $startTime = $value['months'].'-01 00:00:00';
            $lastTime =  date("Y-m-d",strtotime(date('Y-m-d', mktime(23, 59, 59, date('m', strtotime($startTime))+1, 00)))).' 23:59:59';
            $orderCreatedDay = Order::where('cityid',$cityId)
                ->where('refundstatus',0)
                ->where('created_at','>=',$startTime)
                ->where('created_at','<=',$lastTime)
                ->select(DB::raw("sum(orderamount) as orderamount,sum(redenvelopeamount) as redenvelopeamount,DATE_FORMAT(created_at,'%Y-%m-%d') as days,cityid"))
                ->groupBy('days')
                ->orderBy('days','desc')
                ->get();
            if(!empty($orderCreatedDay)){
                foreach($orderCreatedDay as $k=>$v){
                    $dayOrderAmount = $dayOrderAmount + $v['orderamount'];
                    //服务费
                    $feeAmount = (($v->orderamount/2)*$cityRedRatio)*ENV('RECHARGE_FEE');
                    //红包金额
                    $redAmount = ($v['redenvelopeamount']/2)*$cityRedRatio;
                    if($v['days'] >='2017-03-01'){
                        $dayFeeAmount = $dayFeeAmount + sprintf("%.2f",substr($feeAmount,0,(stripos($feeAmount,'.')+3)));
                        $redEnvelopeRefundAmount = $redEnvelopeRefundAmount + sprintf("%.2f",substr($redAmount,0,(stripos($redAmount,'.')+3)));
                        //收入
                        $dayProfitAmount = sprintf("%.2f",substr($feeAmount,0,(stripos($feeAmount,'.')+3))) - sprintf("%.2f",substr($redAmount,0,(stripos($redAmount,'.')+3)));
                        $profitAmount = $profitAmount + $dayProfitAmount;
                    }else{
                        $dayFeeAmount = '0.00';
                        $redEnvelopeRefundAmount = '0.00';
                        $profitAmount = '0.00';
                    }
                }
            }
            $totalProfit = $totalProfit + $profitAmount;
            $newLists[$key]['orderamount'] = $dayOrderAmount;
            $newLists[$key]['fee'] = $dayFeeAmount;
            $newLists[$key]['redenveloperefundamount'] = $redEnvelopeRefundAmount;
            $newLists[$key]['profit'] = $profitAmount;
        }
        $data = [
            'waitwithdrawal' => $accountWaitWithdrawalAmount,
            'withdrawal' => $accountWithdrawalAmount,
            'totalWithdrawal'=>$totalProfit,
            'thismonthwaitwithdrawal' => $thismonthTotalProfitAmount,
            'lists' => $newLists,
            'cityid' =>$cityId,
        ];
        return ['status'=>'success', 'data'=>$data];
    }
    //获取某个月的代理商分润
    public static function agentProfitByMonth($cityId,$months){
        if(!$cityId) {
            return ['status'=>'failed', 'message'=>'参数错误'];
        }
        if(!$months) {
            return ['status'=>'failed', 'message'=>'参数错误'];
        }
        //获取市代红包比率
        $cityRedRatio = City::where('id',$cityId)->pluck('agentratio')->first();
        $cityRedRatio = $cityRedRatio/100;

        $startTime = $months."-01 00:00:00";
        $BeginDate=date($startTime.'-01', strtotime(date("Y-m-d")));
        $lastTime = date('Y-m-d', strtotime("$BeginDate +1 month -1 day")) ." 23:59:59";
        //获取在这个月中订单的金额
        $ordersInfo = DB::select("select DATE_FORMAT(created_at,'%Y-%m-%d') days,sum(orderamount) orderamount,sum(redenvelopeamount) as redenvelopeamount,cityid from 91fbw_orders where cityid='".$cityId."' and refundstatus=0 and created_at >='".$startTime."' and created_at <= '".$lastTime."' group by days order by days desc");
        $newLists = array();
        if(!empty($ordersInfo)){
            foreach($ordersInfo as $key=>$value){
                $feeAmount = ($value->orderamount/2)*$cityRedRatio*ENV('RECHARGE_FEE');
                $fee = sprintf("%.2f",substr($feeAmount,0,(stripos($feeAmount,'.')+3)));
                $redAmount = ($value->redenvelopeamount/2)*$cityRedRatio;
                $redEnvelopeRefundAmount = sprintf("%.2f",substr($redAmount,0,(stripos($redAmount,'.')+3)));
                $profit = $fee -$redEnvelopeRefundAmount;
                array_push($newLists, [
                    'accounttime' => $value->days,
                    'orderamount' => $value->orderamount,
                    'fee' => ($value->days >='2017-03-01')?$fee:'0.00',
                    'redenveloperefundamount' => ($value->days >='2017-03-01')?$redEnvelopeRefundAmount:'0.00',
                    'profit' => ($value->days >='2017-03-01')?$profit:'0.00',
                ]);
            }
        }
       $data = $newLists;
        return ['status'=>'success', 'data'=>$data];
    }
    //导出代理商分润列表
    public static function exportAgentProfit($cityId){
        if(!$cityId) {
            return ['status'=>'failed', 'message'=>'参数错误'];
        }
        //列表
        $data = array();
        //获取市代红包比率
        $cityRedRatio = City::where('id',$cityId)->pluck('agentratio')->first();
        $cityRedRatio = $cityRedRatio/100;
        $newLists = array();
        //通过订单表查找出所有的月份
        $orderCreatedMonth = Order::where('cityid',$cityId)->where('refundstatus',0)->select(DB::raw('DATE_FORMAT(created_at,"%Y-%m") months'))->groupBy('months')->orderBy('months','desc')->get();
        foreach($orderCreatedMonth as $key=>$value){
            $dayOrderAmount = '';
            $dayFeeAmount = '';
            $redEnvelopeRefundAmount = '';
            $profitAmount = '';
            $newLists[$key]['accounttime'] = $value['months'];
            $startTime = $value['months'].'-01 00:00:00';
            $lastTime =  date("Y-m-d",strtotime(date('Y-m-d', mktime(23, 59, 59, date('m', strtotime($startTime))+1, 00)))).' 23:59:59';
            $orderCreatedDay = DB::select("select DATE_FORMAT(created_at,'%Y-%m-%d') months,sum(orderamount) orderamount,sum(redenvelopeamount) as redenvelopeamount,cityid from 91fbw_orders where cityid='".$cityId."' and refundstatus= 0 and created_at >='".$startTime."' and created_at <='".$lastTime."' group by months order by months desc");
            if(!empty($orderCreatedDay)){
                foreach($orderCreatedDay as $k=>$v){
                    $dayOrderAmount = $dayOrderAmount + $v->orderamount;
                    //服务费
                    $feeAmount = (($v->orderamount/2)*$cityRedRatio)*ENV('RECHARGE_FEE');
                    $dayFeeAmount = $dayFeeAmount + sprintf("%.2f",substr($feeAmount,0,(stripos($feeAmount,'.')+3)));
                    //红包金额
                    $redAmount = ($v->redenvelopeamount/2)*$cityRedRatio;
                    $redEnvelopeRefundAmount = $redEnvelopeRefundAmount + sprintf("%.2f",substr($redAmount,0,(stripos($redAmount,'.')+3)));
                    //收入
                    $dayProfitAmount = sprintf("%.2f",substr($feeAmount,0,(stripos($feeAmount,'.')+3))) - sprintf("%.2f",substr($redAmount,0,(stripos($redAmount,'.')+3)));
                    $profitAmount = $profitAmount + $dayProfitAmount;
                }
            }
            $newLists[$key]['orderamount'] = $dayOrderAmount;
            $newLists[$key]['fee'] = ($value['months'] >='2017-03')?$dayFeeAmount:0;
            $newLists[$key]['redenveloperefundamount'] = ($value['months'] >='2017-03')?$redEnvelopeRefundAmount:0;
            $newLists[$key]['profit'] = ($newLists[$key]['accounttime'] >='2017-03')?$profitAmount:0;
        }
        $data = $newLists;
        return ['status'=>'success', 'data'=>$data];
    }
    //导出当月的代理商分润列表
    public static function exportAgentProfitByMonth($cityId,$months){
        if(!$cityId) {
            return ['status'=>'failed', 'message'=>'参数错误'];
        }
        if(!$months) {
            return ['status'=>'failed', 'message'=>'参数错误'];
        }
        //获取市代红包比率
        $cityRedRatio = City::where('id',$cityId)->pluck('agentratio')->first();

        $startTime = $months."-01 00:00:00";
        $BeginDate=date($startTime.'-01', strtotime(date("Y-m-d")));
        $lastTime = date('Y-m-d', strtotime("$BeginDate +1 month -1 day")) ." 23:59:59";
        //获取在这个月中订单的金额
        $ordersInfo = DB::select("select DATE_FORMAT(created_at,'%Y-%m-%d') days,sum(orderamount) orderamount,sum(redenvelopeamount) as redenvelopeamount,cityid from 91fbw_orders where cityid='".$cityId."' and refundstatus=0 and created_at >='".$startTime."' and created_at <= '".$lastTime."' group by days order by days desc");
        $newLists = array();
        if(!empty($ordersInfo)){
            foreach($ordersInfo as $key=>$value){
                $feeAmount = (((($value->orderamount)/2)*($cityRedRatio/100))*ENV('RECHARGE_FEE'));
                $fee = sprintf("%.2f",substr($feeAmount,0,(stripos($feeAmount,'.')+3)));
                $redAmount = ($value->redenvelopeamount/2)*($cityRedRatio/100);
                $redEnvelopeRefundAmount = sprintf("%.2f",substr($redAmount,0,(stripos($redAmount,'.')+3)));
                $profit = $fee -$redEnvelopeRefundAmount;
                array_push($newLists, [
                    'accounttime' => $value->days,
                    'orderamount' => $value->orderamount,
                    'fee' => ($value->days >='2017-03-01')?$fee:'0.00',
                    'redenveloperefundamount' => ($value->days >='2017-03-01')?$redEnvelopeRefundAmount:'0.00',
                    'profit' => ($value->days >='2017-03-01')?$profit:'0.00',
                ]);
            }
        }
        $data = $newLists;
        return ['status'=>'success', 'data'=>$data];
    }
    //代理商待提现
    public static function waitWithdrawal() {
        //生成待提现数据
        self::generateAgentWaitWithdrawal();
        $count = AgentWithdrawal::where('status', 0)->get()->count();
        $sum = AgentWithdrawal::where('status', 0)->sum('accountamount');
        $lists = AgentWithdrawal::where('agent_withdrawal.status', 0)
            ->join('city', 'agent_withdrawal.cityid', '=', 'city.id')
            ->join('admin_users', 'admin_users.id', '=', 'agent_withdrawal.agentuserid')
            ->select('city.name as cityname','city.id as cityid','agent_withdrawal.accountamount','agent_withdrawal.accountmonth', 'agent_withdrawal.id as withdrawalid','admin_users.cardholder',
                'admin_users.bankname', 'admin_users.opencardbank', 'admin_users.bankcity', 'admin_users.bankprovince','admin_users.bankcard')
            ->get();
        $data = [
            'lists' => $lists,
            'sum' => $sum,
            'count' => $count
        ];
        return ['status'=>'success', 'data'=>$data];
    }
    //生成代理商待提现数据
    protected static function generateAgentWaitWithdrawal() {
        //上个月
        $firstDay = date("Y-m-01");
        $startTime = date('Y-m-01', strtotime($firstDay)-86400) . " 00:00:00";
        $lastTime = date("Y-m-d", strtotime($firstDay)-86400) . " 23:59:59";
        $lastMonth = date('Y-m', strtotime($startTime));
        if(AgentWithdrawal::where('accountmonth', $lastMonth)->get()->count() > 0) {
            return ['status'=>'failed', 'message'=>'已经生成了'];
        }
        $rechargeFee = env('RECHARGE_FEE');
        //查询已买单的城市列表
        $cityLists = Order::where('created_at', '>=', $startTime)->where('created_at', '<=', $lastTime)
            ->where('refundstatus', 0)->groupBy('cityid')->select('cityid')->get();
        foreach($cityLists as $cityList) {
            $cityId = $cityList['cityid'];
            $adminUserId = AdminUser::where('cityid', $cityId)
                ->where(function ($query) {
                    $query->where('roleid',4)->orwhere('id', 30);
                })
                ->pluck('id')->first();
            if(!$adminUserId) {
                //该城市没有开通代理商
                continue;
            }
            $agentRatio = City::where('id', $cityId)->pluck('agentratio')->first();
            if(!$agentRatio) {
                //没有设置代理商比例
                continue;
            }
            //结款金额 = (订单 * env('RECHARGE_FEE') * agentratio /100 - 已使用红包金额) / 2;
//            $sumOrder = Order::where('cityid', $cityId)->where('refundstatus', 0)
//                ->where('created_at', '>=', $startTime)->where('created_at', '<=', $lastTime)->sum('orderamount');
//            $sumRedEnvelope = Order::where('cityid', $cityId)->where('refundstatus', 0)
//                ->where('created_at', '>=', $startTime)->where('created_at', '<=', $lastTime)->sum('redenvelopeamount');
//            $sumRedEnvelope = $sumRedEnvelope *($agentRatio/100);
//            $sumRedEnvelope = sprintf("%.2f",substr($sumRedEnvelope,0,(stripos($sumRedEnvelope,'.')+3)));
//            $feeAmount = (($sumOrder * $rechargeFee * $agentRatio / 100) - $sumRedEnvelope)/2;
//            $money = sprintf("%.2f",sprintf("%.2f",substr($feeAmount,0,(stripos($feeAmount,'.')+3))));
//            $agentWithdrawal = AgentWithdrawal::create([
//                'cityid' => $cityId,
//                'agentuserid' => $adminUserId,
//                'accountamount' => $money,
//                'accountmonth' => $lastMonth,
//                'status' => 0,
//                'adminid' => 0,
//            ]);
//            $agentWithdrawal->save();
            $sumMoney = '0.00';
            $cityOrderAmount = order::where('cityid',$cityId)
                ->where('refundstatus',0)
                ->where('created_at', '>=', $startTime)
                ->where('created_at', '<=', $lastTime)
                ->select(DB::raw('sum(orderamount) as orderamount, sum(redenvelopeamount) as redenvelopeamount,DATE_FORMAT(created_at,"%Y-%m-%d") as day'))
                ->groupBy("day")
                ->get();
            if(!empty($cityOrderAmount)){
                foreach($cityOrderAmount as $key=>$value){
                    $sumRedEnvelope = $value['redenvelopeamount']/2 *($agentRatio/100);
                    $sumRedEnvelope = sprintf("%.2f",substr($sumRedEnvelope,0,(stripos($sumRedEnvelope,'.')+3)));
                    $feeAmount = ($value['orderamount'] * $rechargeFee * $agentRatio / 100)/2;
                    $money = sprintf("%.2f",sprintf("%.2f",substr($feeAmount,0,(stripos($feeAmount,'.')+3)))) - $sumRedEnvelope;
                    $sumMoney = $sumMoney + $money;
                }
            }
            $agentWithdrawal = AgentWithdrawal::create([
                'cityid' => $cityId,
                'agentuserid' => $adminUserId,
                'accountamount' => $sumMoney,
                'accountmonth' => $lastMonth,
                'status' => 0,
                'adminid' => 0,
            ]);
            $agentWithdrawal->save();
        }
        return ['status'=>'success', 'message'=>'更新数据成功'];
    }
    //代理商待提现导出
    public static function exportAgentShopWaitWithdrawal(){
        $cityInfo = AgentWithdrawal::where('agent_withdrawal.status', 0)
            ->join('city', 'agent_withdrawal.cityid', '=', 'city.id')
            ->join('admin_users', 'admin_users.id', '=', 'agent_withdrawal.agentuserid')
            ->select('city.name as cityname','city.id as cityid','agent_withdrawal.accountamount', 'agent_withdrawal.accountmonth', 'admin_users.cardholder', 'admin_users.bankname',
                'admin_users.opencardbank', 'admin_users.bankcity', 'admin_users.bankprovince','admin_users.bankcard')
            ->get();
        //列表
        $startTime = date('Y-m').'-01 00:00:00';
        $newLists = [];
        foreach($cityInfo as $key=>$value){
            $newLists[$key]['cityname'] = $value['cityname'];
            $newLists[$key]['agentusername'] = $value['cardholder'];
            $newLists[$key]['cardholder'] = $value['cardholder'];
            $newLists[$key]['bankname'] = $value['bankname'];
            $newLists[$key]['bankcard'] = $value['bankcard'];
            $newLists[$key]['opencardbank'] = $value['opencardbank'];
            $newLists[$key]['bankprovince'] = $value['bankprovince'];
            $newLists[$key]['bankcity'] = $value['bankcity'];
            $newLists[$key]['waitdrawalamount'] = $value['accountamount'];
            $newLists[$key]['cityid'] = $value['cityid'];
        }
       return ['status'=>'success','data'=>$newLists];
    }
        //代理商已提现
    public static function withdrawal() {
        //已提现笔数
        $count = AgentWithdrawal::where('agent_withdrawal.status', 1)->get()->count();
        $sum = AgentWithdrawal::where('agent_withdrawal.status', 1)->sum('accountamount');
        $lists = AgentWithdrawal::where('agent_withdrawal.status', 1)
            ->join('city', 'agent_withdrawal.cityid', '=', 'city.id')
            ->join('admin_users', 'admin_users.id', '=', 'agent_withdrawal.agentuserid')
            ->select('city.name as cityname','city.id as cityid','agent_withdrawal.accountamount','agent_withdrawal.accountmonth', 'admin_users.cardholder', 'admin_users.bankname',
                'admin_users.opencardbank', 'admin_users.bankcity', 'admin_users.bankprovince','admin_users.bankcard')
            ->get();
        $data = [
            'count' => $count,
            'sum' => $sum,
            'lists' => $lists
        ];
        return ['status'=>'success', 'data'=>$data];
    }
    //代理商已提现导出
    public static function exportAgentShopWithdrawal(){
        $lists = AgentWithdrawal::where('agent_withdrawal.status', 1)
            ->join('city', 'agent_withdrawal.cityid', '=', 'city.id')
            ->join('admin_users', 'admin_users.id', '=', 'agent_withdrawal.agentuserid')
            ->select('city.name as cityname','city.id as cityid','agent_withdrawal.accountamount','agent_withdrawal.accountmonth', 'admin_users.cardholder', 'admin_users.bankname',
                'admin_users.opencardbank', 'admin_users.bankcity', 'admin_users.bankprovince','admin_users.bankcard')
            ->get();
        return ['status'=>'success','data'=>$lists];
    }
    //代理商对账单
    public static function agentStatement($startTime,$lastTime) {
        //代理商数
        $agentCount = AdminUser::where('roleid', 4)->orwhere('admin_users.id',30)->where('admin_users.status',1)->where('cityid','<>',' ')->join('city','city.id','=','admin_users.cityid')->count();

        //已提现金额和待提现金额
        $witdrawalamountAgent = AgentWithdrawal::where('status',1)->sum('accountamount');
        $waitthdrawalamountAgent = AgentWithdrawal::where('status',0)->sum('accountamount');
        //获取对账单列表
        $newLists = [];
        $totalSumMoney = '0.00';
        $thismonthTotalSumMoney = '0.00';
        $cityInfo = AdminUser::where('roleid',4)->orwhere('admin_users.id',30)->where('admin_users.status',1)->join('city','city.id','=','admin_users.cityid')->select('admin_users.cityid','city.name','agentratio')->get();
        if(!empty($cityInfo)){
            foreach($cityInfo as $key=>$value){
                //获取总的收入和本月的收入
                $cityId = $value['cityid'];
                $agentRatio = $value['agentratio']/100;
                $rechargeFee = env('RECHARGE_FEE');

                //获取本月服务费
                $thismonthOrderTotalFeeAmount = '';
                $thismonthTotalRedAmount = '';
                $thismonthStartTime = date('Y-m').'-01 00:00:00';
                $thismonthLastTime = date('Y-m-d', strtotime("$thismonthStartTime +1 month -1 day")) ." 23:59:59";
                $thismonthOrderAmount = DB::select("select DATE_FORMAT(created_at,'%Y-%m-%d') months,sum(orderamount) orderamount,sum(redenvelopeamount) as redenvelopeamount,cityid from 91fbw_orders where cityid='".$cityId."' and refundstatus= 0 and created_at >= '2017-03-01 00:00:00' and created_at >='".$thismonthStartTime."' and created_at <='".$thismonthLastTime."' group by months order by months desc");
                foreach($thismonthOrderAmount as $orderkey=>$ordervalue){
                    $thismonthOrderFeeAmount = ($ordervalue->orderamount/2)*$agentRatio*ENV('RECHARGE_FEE');
                    $thismonthOrderTotalFeeAmount = $thismonthOrderTotalFeeAmount + sprintf("%.2f",substr($thismonthOrderFeeAmount,0,(stripos($thismonthOrderFeeAmount,'.')+3)));
                    $thismonthOrderRedAmount = ($ordervalue->redenvelopeamount/2)*$agentRatio;
                    $thismonthTotalRedAmount = $thismonthTotalRedAmount + sprintf("%.2f",substr($thismonthOrderRedAmount,0,(stripos($thismonthOrderRedAmount,'.')+3)));
                }
                $thismonthTotalProfitAmount = $thismonthOrderTotalFeeAmount - $thismonthTotalRedAmount;
                $thismonthTotalSumMoney = $thismonthTotalSumMoney + $thismonthTotalProfitAmount;
                $sumMoney = '0.00';
                $orderAmount = '0.00';
                $sumOrderRedEnvelope = '0.00';
                $orderFeeAmount = '0.00';
                $cityOrderAmount = order::where('cityid',$cityId)
                    ->where('refundstatus',0)
                    ->where('created_at', '>=', $startTime)
                    ->where('created_at', '<=', $lastTime)
                    ->select(DB::raw('sum(orderamount) as orderamount, sum(redenvelopeamount) as redenvelopeamount,DATE_FORMAT(created_at,"%Y-%m-%d") as day'))
                    ->groupBy("day")
                    ->get();
                if(!empty($cityOrderAmount)){
                    foreach($cityOrderAmount as $k=>$v){
                        $orderAmount = $orderAmount + $v['orderamount'];
                        if($v['day'] >= '2017-03-01'){
                            $sumRedEnvelope = $v['redenvelopeamount']/2 *$agentRatio;
                            $feeAmount = ($v['orderamount'] * $rechargeFee * $agentRatio)/2;
                            $sumOrderRedEnvelope = $sumOrderRedEnvelope + sprintf("%.2f",substr($sumRedEnvelope,0,(stripos($sumRedEnvelope,'.')+3)));
                            $orderFeeAmount = $orderFeeAmount + sprintf("%.2f",substr($feeAmount,0,(stripos($feeAmount,'.')+3)));
                            $money = sprintf("%.2f",substr($feeAmount,0,(stripos($feeAmount,'.')+3))) - sprintf("%.2f",substr($sumRedEnvelope,0,(stripos($sumRedEnvelope,'.')+3)));
                            $sumMoney = $sumMoney + $money;
                        }
                    }
                }
                $totalSumMoney = $totalSumMoney + $sumMoney;
                $newLists[$key]['cityid'] = $cityId;
                $newLists[$key]['cityname'] = $value['name'];
                $newLists[$key]['ordercount'] = Order::where('cityid', $cityId)->where('refundstatus',0)->where('created_at','>=',$startTime)->where('created_at','<=',$lastTime)->count();
                $newLists[$key]['orderamount'] = $orderAmount;
                $newLists[$key]['fee'] = $orderFeeAmount;
                $newLists[$key]['redenvelopeamount'] = $sumOrderRedEnvelope;
                $newLists[$key]['agentprofit'] = $sumMoney;
                $newLists[$key]['withdrawalamount'] = AgentWithdrawal::where('cityid',$cityId)->where('status',1)->where('accountmonth','>=',date("Y-m",strtotime($startTime)))->where('accountmonth','<=',date("Y-m",strtotime($lastTime)))->sum('accountamount');
                $newLists[$key]['waitwithdrawalamount'] = AgentWithdrawal::where('cityid',$cityId)->where('status',0)->where('accountmonth','>=',date("Y-m",strtotime($startTime)))->where('accountmonth','<=',date("Y-m",strtotime($lastTime)))->sum('accountamount');
            }
        }
        $data = [
            'agentcount' =>$agentCount,
            'agentwithdrawal' => $witdrawalamountAgent,
            'agentwaitwithdrawal' => $waitthdrawalamountAgent,
            'agentProfitTotal'=>$totalSumMoney,
            'thismonthAmountTotal'=>$thismonthTotalSumMoney,
            'lists' => $newLists,
        ];
        return ['status'=>'success','data'=>$data];
    }
    //代理商利润（元）
    protected static function getTotalAgentProfit($cityId='') {
        return 0.00;
        //充值服务费

        //红包金额

        //已退款金额

    }
    //代理商对账单详情信息
    public static function agengtStatementDetails($cityId,$startTime,$lastTime){
        if(!$cityId) {
            return ['status'=>'failed', 'message'=>'参数错误'];
        }
        $dataList = [];
        $data = [];
        $totalOrderAmount = '';
        $totalFeeAmount = '';
        $totalRedAmount = '';
        $totalAgentPorfit = '';
        //获得城市代理商比率
        $agentratioCity = City::where('id',$cityId)->pluck('agentratio')->first();
        $agentratioCity = $agentratioCity/100;
        $orderLists = Order::where('cityid',$cityId)
            ->where('refundstatus',0)
            ->where('created_at','>=',$startTime)
            ->where('created_at','<=',$lastTime)
            ->select(DB::raw("DATE_FORMAT(created_at,'%Y-%m-%d') days,sum(orderamount) orderamount,sum(redenvelopeamount) as redenvelopeamount,cityid"))
            ->groupBy('days')
            ->orderBy('days','desc')
            ->get();
        foreach($orderLists as $key=>$value){
            $dataList[$key]['days'] = $value['days'];
            $dataList[$key]['orderAmount'] = $value['orderamount'];
            $fee = ($value['orderamount']/2)*$agentratioCity*ENV('RECHARGE_FEE');
            $fee = sprintf("%.2f",substr($fee,0,(stripos($fee,'.')+3)));
            $dataList[$key]['fee'] = ($value['days'] >= '2017-03-01')?$fee:'0.00';
            $redAmountDetail = ($value['redenvelopeamount']/2)*$agentratioCity;
            $agentamount = ($value['days'] >= '2017-03-01')?sprintf("%.2f",substr($redAmountDetail,0,(stripos($redAmountDetail,'.')+3))):'0.00';
            $dataList[$key]['redEnvelopeAmount'] = $agentamount;
            $agentProfitList = $fee - $agentamount;
            if($value['days'] >= '2017-03-01'){
                $totalOrderAmount = $totalOrderAmount + $value['orderamount'];
                $totalFeeAmount = $totalFeeAmount + $fee;
                $totalRedAmount = $totalRedAmount + sprintf("%.2f",substr($redAmountDetail,0,(stripos($redAmountDetail,'.')+3)));
                $totalAgentPorfit = $totalAgentPorfit + $agentProfitList;
            }
            $dataList[$key]['agentProfit'] = ($value['days'] >= '2017-03-01')?$agentProfitList:'0.00';
            $yearMonth = date('Y-m',strtotime($value['days']));
            $agentStatus = AgentWithdrawal::where('cityid',$value['cityid'])->where('accountmonth',$yearMonth)->select('status','accountmonth')->first();
            $dataList[$key]['statusname'] = $agentStatus['status'] == 1?'已提现':'待提现';
            $dataList[$key]['accounttime'] = !empty($agentStatus['accountmonth']) && $agentStatus['accountmonth'] != '0000-00-00 00:00:00' && $agentStatus['status'] ==1 ?$agentStatus['accountmonth']:'';
        }
        $data = [
            'orderAmount' => $totalOrderAmount,
            'fee' => $totalFeeAmount,
            'agentwaitwithdrawal' => $totalRedAmount,
            'agentProfit'=>$totalAgentPorfit,
            'cityname'=>City::where('id',$cityId)->pluck('name')->first(),
            'startTime'=>$startTime,
            'lastTime'=>$lastTime,
            'lists' => $dataList,
        ];
        return ['status'=>'success', 'data'=>$data];
    }
    //导出代理商对账单
    public static function exportAgentStatement($startTime,$lastTime){
        //代理商数
        $agentCount = AdminUser::where('roleid', 4)->orwhere('admin_users.id',30)->where('admin_users.status',1)->where('cityid','<>',' ')->join('city','city.id','=','admin_users.cityid')->count();
        //获取对账单列表
        $newLists = [];
        $cityInfo = AdminUser::where('roleid',4)->orwhere('admin_users.id',30)->where('admin_users.status',1)->join('city','city.id','=','admin_users.cityid')->select('admin_users.cityid','city.name','agentratio')->get();
        if(!empty($cityInfo)){
            foreach($cityInfo as $key=>$value){
                $cityId = $value['cityid'];
                $agentRatio = $value['agentratio']/100;
                $rechargeFee = env('RECHARGE_FEE');
                $sumMoney = '0.00';
                $orderAmount = '0.00';
                $sumOrderRedEnvelope = '0.00';
                $orderFeeAmount = '0.00';
                $cityOrderAmount = order::where('cityid',$cityId)
                    ->where('refundstatus',0)
                    ->where('created_at', '>=', $startTime)
                    ->where('created_at', '<=', $lastTime)
                    ->select(DB::raw('sum(orderamount) as orderamount, sum(redenvelopeamount) as redenvelopeamount,DATE_FORMAT(created_at,"%Y-%m-%d") as day'))
                    ->groupBy("day")
                    ->get();
                if(!empty($cityOrderAmount)){
                    foreach($cityOrderAmount as $k=>$v){
                        $orderAmount = $orderAmount + $v['orderamount'];
                        if($v['day'] >= '2017-03-01'){
                            $sumRedEnvelope = $v['redenvelopeamount']/2 *$agentRatio;
                            $feeAmount = ($v['orderamount'] * $rechargeFee * $agentRatio)/2;
                            $sumOrderRedEnvelope = $sumOrderRedEnvelope + sprintf("%.2f",substr($sumRedEnvelope,0,(stripos($sumRedEnvelope,'.')+3)));
                            $orderFeeAmount = $orderFeeAmount + sprintf("%.2f",substr($feeAmount,0,(stripos($feeAmount,'.')+3)));
                            $money = sprintf("%.2f",substr($feeAmount,0,(stripos($feeAmount,'.')+3))) - sprintf("%.2f",substr($sumRedEnvelope,0,(stripos($sumRedEnvelope,'.')+3)));
                            $sumMoney = $sumMoney + $money;
                        }
                    }
                }
                $newLists[$key]['cityid'] = $cityId;
                $newLists[$key]['cityname'] = $value['name'];
                $newLists[$key]['ordercount'] = Order::where('cityid', $cityId)->where('refundstatus',0)->where('created_at','>=',$startTime)->where('created_at','<=',$lastTime)->count();
                $newLists[$key]['orderamount'] = $orderAmount;
                $newLists[$key]['fee'] = $orderFeeAmount;
                $newLists[$key]['redenvelopeamount'] = $sumOrderRedEnvelope;
                $newLists[$key]['agentprofit'] = $sumMoney;
                $newLists[$key]['withdrawalamount'] = AgentWithdrawal::where('cityid',$cityId)->where('status',1)->where('accountmonth','>=',date("Y-m",strtotime($startTime)))->where('accountmonth','<=',date("Y-m",strtotime($lastTime)))->sum('accountamount');
                $newLists[$key]['waitwithdrawalamount'] = AgentWithdrawal::where('cityid',$cityId)->where('status',0)->where('accountmonth','>=',date("Y-m",strtotime($startTime)))->where('accountmonth','<=',date("Y-m",strtotime($lastTime)))->sum('accountamount');
            }
        }
        return ['status'=>'success','data'=>$newLists];
    }
    //对对账明细进行excel导出
    public static function exportAgentStatementDetails($cityId,$startTime,$lastTime){
        if(!$cityId) {
            return ['status'=>'failed', 'message'=>'参数错误'];
        }
        $dataList = [];
        //获得城市代理商比率
        $agentratioCity = City::where('id',$cityId)->pluck('agentratio')->first();
        $agentratioCity = $agentratioCity/100;
        $orderLists = Order::where('cityid',$cityId)
            ->where('refundstatus',0)
            ->where('created_at','>=',$startTime)
            ->where('created_at','<=',$lastTime)
            ->select(DB::raw("DATE_FORMAT(created_at,'%Y-%m-%d') days,sum(orderamount) orderamount,sum(redenvelopeamount) as redenvelopeamount,cityid"))
            ->groupBy('days')
            ->orderBy('days','desc')
            ->get();
        foreach($orderLists as $key=>$value){
            $dataList[$key]['days'] = $value['days'];
            $dataList[$key]['orderAmount'] = $value['orderamount'];
            $fee = ($value['orderamount']/2)*$agentratioCity*ENV('RECHARGE_FEE');
            $fee = sprintf("%.2f",substr($fee,0,(stripos($fee,'.')+3)));
            $dataList[$key]['fee'] = ($value['days'] >= '2017-03-01')?$fee:'0.00';
            $redAmountDetail = ($value['redenvelopeamount']/2)*$agentratioCity;
            $agentamount = ($value['days'] >= '2017-03-01')?sprintf("%.2f",substr($redAmountDetail,0,(stripos($redAmountDetail,'.')+3))):'0.00';
            $dataList[$key]['redEnvelopeAmount'] = $agentamount;
            $agentProfitList = $fee - $agentamount;
            $dataList[$key]['agentProfit'] = ($value['days'] >= '2017-03-01')?$agentProfitList:'0.00';
            $yearMonth = date('Y-m',strtotime($value['days']));
            $agentStatus = AgentWithdrawal::where('cityid',$value['cityid'])->where('accountmonth',$yearMonth)->select('status','accountmonth')->first();
            $dataList[$key]['statusname'] = $agentStatus['status'] == 1?'已提现':'待提现';
            $dataList[$key]['accounttime'] = !empty($agentStatus['accountmonth']) && $agentStatus['accountmonth'] != '0000-00-00 00:00:00' && $agentStatus['status'] ==1 ?$agentStatus['accountmonth']:'';
        }
        return ['status'=>'success', 'data'=>$dataList];
    }
    //代理商帐号管理
    public static function agentReceivableManage(){
        //获取代理商信息
        $agentInfo = AdminUser::where('roleid',4)
            ->where('admin_users.status',1)
            ->orwhere('admin_users.id','30')
            ->join('city','city.id','=','admin_users.cityid')
            ->select('admin_users.id as adminid','city.name as cityname','cardholder','bankcard','bankname','opencardbank','bankprovince','bankcity')
            ->get();
        return ['status'=>'success','data'=>$agentInfo];
    }
    //获取银行信息
    public static function getBankProvince() {
        $allProvince = AreaTool::getBankProvinceAndCity();
        $data = [];
        foreach($allProvince as $key=>$value) {
            array_push($data, $key);
        }
        return ['status'=>'success','data'=>$data];
    }
    public static function getBankCity($provincename) {
        $allProvince = AreaTool::getBankProvinceAndCity();
        $data = [];
        foreach($allProvince as $key => $value) {
            if($provincename == $key) {
                $data = explode(',', $value);
            }
        }
        return ['status'=>'success','data'=>$data];
    }
    //对代理商账号进行修改
    public static function endAgent($adminid,$data){
        if (!$adminid || !$data) {
            return ['status' => 'failed', 'message' => '请求数据不完整！'];
        }
        AdminUser::where('id',$adminid)->update($data);
        return ['status' => 'success', 'message'=>'修改成功'];
    }
    //给代理商打款
    public static function agentShopWaitWithdrawalRemit($withdrawalId, $cityId){
        if (!$withdrawalId||!$cityId) {
            return ['status' => 'failed', 'message' => '请求数据不完整！'];
        }
        $agentWithdrawal = AgentWithdrawal::where('id',$withdrawalId)->where('status',0)->where('cityid', $cityId)->first();
        if(!$agentWithdrawal){
            return ['status' => 'failed', 'message' => '请求数据错误！'];
        }
        $firstDay = date('Y-m-01') . " 00:00:00";
        $orderAmount = Order::where('cityid', $cityId)->where('created_at', '>=', $firstDay)->sum('orderamount');
        $redEnvelopeAmount = Order::where('cityid', $cityId)->where('created_at', '>=', $firstDay)->sum('redenvelopeamount');
        $rechargeFee = env('RECHARGE_FEE');
        $agentRatio = City::where('id', $cityId)->pluck('agentratio')->first();
        $balance = round($orderAmount * $rechargeFee * $agentRatio /100 - $redEnvelopeAmount);
        $balance = round($balance/2, 2);
        $agentWithdrawal->status = 1;
        $agentWithdrawal->adminid = Auth::guard('adminweb')->id();
        $agentWithdrawal->accounttime = date('Y-m-d H:i:s');
        $agentWithdrawal->balance = $balance;
        $agentWithdrawal->save();
        return ['status' => 'success', 'message'=>'打款成功'];
    }
    //导出代理商对账单
    public static function exportAgentCollectionAccountWithdrawal(){
        //获取代理商信息
        $agentInfo = AdminUser::where('roleid',4)
            ->where('admin_users.status',1)
            ->orwhere('admin_users.id','30')
            ->join('city','city.id','=','admin_users.cityid')
            ->select('admin_users.id as adminid','city.name as cityname','cardholder','bankcard','bankname','opencardbank','bankprovince','bankcity')
            ->get();
        return ['status'=>'success','data'=>$agentInfo];
    }

}